package org.zn.etl.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Optional;
import java.util.stream.Collectors;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.zn.datax.common.entity.ColumnInfo;
import org.zn.datax.common.entity.TableInfo;
import org.zn.etl.datasource.entity.DataSourceInfo;
import org.zn.etl.jdbc.SqlBuilder;
import org.zn.etl.job.dao.DataMappingMapper;
import org.zn.etl.job.dao.DataSourceInfoMapper;
import org.zn.etl.job.dao.EtlJobMapper;
import org.zn.etl.job.dao.JobGroupMapper;
import org.zn.etl.job.dao.JobGroupRelMapper;
import org.zn.etl.job.dao.MappingColumnMapper;
import org.zn.etl.job.entity.BatchJobGroupForm;
import org.zn.etl.job.entity.BatchJobGroupForm.BatchDataMappings;
import org.zn.etl.job.entity.ColumnMappingForm;
import org.zn.etl.job.entity.DataMapping;
import org.zn.etl.job.entity.EtlJob;
import org.zn.etl.job.entity.JobAnalyseForm;
import org.zn.etl.job.entity.JobGroup;
import org.zn.etl.job.entity.po.DataMappingPo;
import org.zn.etl.job.entity.po.DataSourceInfoPo;
import org.zn.etl.job.entity.po.EtlJobPo;
import org.zn.etl.job.entity.po.JobGroupPo;
import org.zn.etl.job.entity.po.JobGroupRelPo;
import org.zn.etl.job.entity.po.MappingColumnPo;
import org.zn.etl.misc.BeanCopierUtils;
import org.zn.etl.misc.UuidUtils;
import org.zn.etl.service.EtlAnalyseService;
import org.zn.etl.service.EtlPersistService;

import com.github.pagehelper.PageHelper;
import com.google.common.collect.Lists;

import lombok.extern.slf4j.Slf4j;

@Service
@Slf4j
public class EtlPersistServiceImpl  implements EtlPersistService{
	
	private static String SOUR="source";
	private static String TARG="target";
	
	@Autowired
	private DataSourceInfoMapper dataSourceMapper;
	
	@Autowired
	private EtlJobMapper etlJobMapper;
	
	@Autowired
	private DataMappingMapper dataMappingMapper;
	
	@Autowired
	private MappingColumnMapper mappingColumnMapper;
	
	@Autowired
	private JobGroupMapper jobGroupMapper;
	
	@Autowired
	private JobGroupRelMapper jobGroupRelMapper;
	
	@Autowired
	private EtlAnalyseService analyseService ;
	
	@Transactional(rollbackFor = Exception.class)
	public EtlJobPo saveEtlJob(EtlJob job) {
		
		EtlJobPo ejp=BeanCopierUtils.copy(job, new EtlJobPo());
		
		Date ct=new Date();
		
		if(job.getSource()!=null&&job.getSource().getKeyName()!=null) {
			Long source=queryDataSourceByName(job.getSource().getKeyName()).getId(); 
			ejp.setSourceId(source);
		}
		
		if(job.getTarget()!=null&&job.getTarget().getKeyName()!=null) {
			Long target=queryDataSourceByName(job.getTarget().getKeyName()).getId(); 
			ejp.setTargetId(target);			
		}

		if(Objects.isNull(ejp.getId()) ) {
			ejp.setCreateTime(ct);
		   //ejp.setId(UuidUtils.generateIdString());
			job.setId(ejp.getId());
			etlJobMapper.insert(ejp);			
		}else {
			ejp.setUpdateTime(ct);
			etlJobMapper.updateByPrimaryKey(ejp);
		}
		
		List<DataMapping>  dmlist=	job.getDataMappings();
		
		if(dmlist==null) {
			return ejp;
		}
		
		
		for (DataMapping dm : dmlist) {
			
			DataMappingPo dmp=new DataMappingPo();
			dmp.setJobId(ejp.getId());
			
			if(dm.getSourceTable() !=null) {	
			dmp.setSourceTable(dm.getSourceTable().getTable());
			dmp.setSourceSchema(dm.getSourceTable().getSchema());
			dmp.setSourcedbName(dm.getSourceTable().getDbName());
			dmp.setSourcePk(dm.getSourceTable().getPk());
			dmp.setSourceModifyTimeKey(dm.getSourceTable().getModifyTimeKey());
			dmp.setSourceCreateTimeKey(dm.getSourceTable().getCreateTimeKey());
			dmp.setSourceSplitPk(dm.getSourceTable().getSplitPk());
			}
			dmp.setSourceSql(dm.getSourceSql());
			
			if(dm.getTargetTable() !=null) {	
			dmp.setTargetTable(dm.getTargetTable().getTable());
			dmp.setTargetSchema(dm.getTargetTable().getSchema());
			dmp.setTargetDbname(dm.getTargetTable().getDbName());
			dmp.setTargetPk(dm.getTargetTable().getPk());
			dmp.setTargetModifyTimeKey(dm.getTargetTable().getModifyTimeKey());
			dmp.setTargetCreateTimeKey(dm.getTargetTable().getCreateTimeKey());
			dmp.setTargetSplitPk(dm.getTargetTable().getSplitPk());
			}
			dmp.setTargetSql(dm.getTargetSql());
		
			if(dm.getId()==null) {
				dmp.setCreateTime(ct);
				//dmp.setId(UuidUtils.generateIdString());
				dataMappingMapper.insert(dmp);				
			}else {
				dataMappingMapper.updateByPrimaryKey(dmp);
			}
			

			List<Pair<ColumnInfo, ColumnInfo>>  cmpList=  dm.getColumnMappingPairs();
			
			if(CollectionUtils.isNotEmpty(cmpList)) {
				
			int sort=1;	
				
			for (Pair<ColumnInfo, ColumnInfo>  cmp : cmpList) {
				
				MappingColumnPo mcpL= BeanCopierUtils.copy(cmp.getLeft(), new MappingColumnPo());
				mcpL.setBelong(SOUR);
				//mcpL.setId(UuidUtils.generateIdString());
				mcpL.setCreateTime(ct);
				mcpL.setDatamappingId(dmp.getId());
				mcpL.setSort(sort);
				
				MappingColumnPo mcpR= BeanCopierUtils.copy(cmp.getRight(), new MappingColumnPo());
				mcpR.setBelong(TARG);
				//mcpR.setId(UuidUtils.generateIdString());
				mcpR.setCreateTime(ct);
				mcpR.setDatamappingId(dmp.getId());
				mcpR.setSort(sort);
							
				mappingColumnMapper.insert(mcpL);
				mappingColumnMapper.insert(mcpR);
				sort++;
				
				}
			
			}
		}
		
		return ejp;
		
	}
	
	


	@Override
	public int delEtlJob(Long id, String name) {
		// TODO Auto-generated method stub
		
		if(Objects.nonNull(id)) id=etlJobMapper.selectByName(name).getId();
		
		int i=	etlJobMapper.deleteByPrimaryKey(id);
		
		return i;
	}
	
	
	
	
	public List<EtlJobPo>  queryEtlJobList(Integer pageNum, Integer pageSize) {
		
		PageHelper.startPage(pageNum, pageSize);
		List<EtlJobPo> list= etlJobMapper.selectAll();
		
		return list;
	}
	
	
	public EtlJob queryEtlJobById(Long id) {
		
		EtlJobPo ejp=etlJobMapper.selectByPrimaryKey(id);
		
		return etlJob(ejp);
		
	} 

	
	public EtlJob queryEtlJob(String jobName) {
		
		EtlJobPo ejp=etlJobMapper.selectByName(jobName);
		
		return etlJob(ejp);
		
	}	
	
	
	private EtlJob etlJob(EtlJobPo ejp) {	
				
		EtlJob ej=BeanCopierUtils.copy(ejp, new EtlJob());
		
		ej.setSource( BeanCopierUtils.copy(dataSourceMapper.selectByPrimaryKey(ejp.getSourceId()), new DataSourceInfo())  ); 
		ej.setTarget(  BeanCopierUtils.copy( dataSourceMapper.selectByPrimaryKey(ejp.getTargetId()),new DataSourceInfo()));
		
		List<DataMappingPo> list=	dataMappingMapper.selectByJobid(ejp.getId());

		List<DataMapping> dmlist=list.stream().map(it->{
			
			DataMapping dm=new DataMapping();
			
			TableInfo ts=new TableInfo();
			TableInfo tt=new TableInfo();
			
			ts.setTable(it.getSourceTable());
			ts.setDbName(it.getSourcedbName());
			ts.setPk(it.getSourcePk());
			ts.setSchema(it.getSourceSchema());
			
			ts.setSplitPk(it.getSourceSplitPk());
			ts.setCreateTimeKey(it.getSourceCreateTimeKey());
			ts.setModifyTimeKey(it.getSourceModifyTimeKey());
			ts.setMetaTable(it.getSourceTableTemp());
			
			tt.setTable(it.getTargetTable());
			tt.setDbName(it.getTargetDbname());
			tt.setPk(it.getTargetPk());
			tt.setSchema(it.getTargetSchema());
			tt.setSplitPk(it.getTargetSplitPk());
			tt.setCreateTimeKey(it.getTargetCreateTimeKey());
			tt.setModifyTimeKey(it.getTargetModifyTimeKey());
			tt.setMetaTable(it.getTargetTableTemp());

			dm.setSourceSql(it.getSourceSql());
			dm.setTargetSql(it.getTargetSql());
			dm.setId(it.getId());
			dm.setDataTransformer(it.getTransformer());
			
			List<Pair<ColumnInfo, ColumnInfo>>  cmpList=new ArrayList<>();
			
			List<MappingColumnPo>  collist=mappingColumnMapper.selectBydmId(it.getId());
			
			Map<Integer, MappingColumnPo[]> colMap=new LinkedHashMap<>();
					
			for (MappingColumnPo mcp : collist) {

				MappingColumnPo[] arr =colMap.get(mcp.getSort());
				if(arr==null) {
					 arr=new MappingColumnPo[2];
					 arr[0]=mcp;
				}
				else {
					 arr[1]=mcp;
				}
				
				colMap.put(mcp.getSort(), arr);
				
			}
					
			for (MappingColumnPo[] mcpp:colMap.values()) {
			
				MappingColumnPo  ci1=mcpp[0];
				MappingColumnPo  ci2=mcpp[1];
				
				ColumnInfo cl=null;
				ColumnInfo cr=null;
				
				if(ci1!=null) {
					if(ci1.getBelong().equals(SOUR)) {
						//cl=	BeanCopierUtils.copy(ci1, new ColumnInfo());	
						cl=new ColumnInfo();
						cl.setByMappingColumnPo(ci1);
						
					}
					else {
						cr=new ColumnInfo();
						cr.setByMappingColumnPo(ci2);
						//cr=	BeanCopierUtils.copy(ci2, new ColumnInfo());						
					}	
				}
				
				if(ci2!=null) {
					if(ci2.getBelong().equals(TARG)) {	
						
						//cr=	BeanCopierUtils.copy(ci2, new ColumnInfo());
						
						cr=new ColumnInfo();
						cr.setByMappingColumnPo(ci2);
						
					}else {
						
						//cl=	BeanCopierUtils.copy(ci1, new ColumnInfo());
						cl=new ColumnInfo();
						cl.setByMappingColumnPo(ci1);
						
					}
				}
				Pair<ColumnInfo, ColumnInfo> pair=Pair.of(cl, cr);
				cmpList.add(pair);
			}

			dm.setColumnMappingPairs(cmpList);
			dm.setSourceTable(ts);
			dm.setTargetTable(tt);
			
			return dm;
			
		}).collect(Collectors.toList());
		
		
		ej.setDataMappings(dmlist);
		
		
		return ej;
	}
	
	
	public DataSourceInfoPo addDataSourceInfo(DataSourceInfo dsi) {
		
		DataSourceInfoPo po=BeanCopierUtils.copy(dsi, new DataSourceInfoPo());
		
		po.setCreateTime(new Date());
		//po.setId(UuidUtils.generateIdString());

		dataSourceMapper.insert(po);
		
		return po;
	}
	
	public int updateDataSourceInfo(DataSourceInfoPo po) {
		
		po.setUpdateTime(new Date());
		
		return dataSourceMapper.updateByPrimaryKey(po);
	}
	
	public DataSourceInfoPo queryById(Long id) {
		
		DataSourceInfoPo dip=dataSourceMapper.selectByPrimaryKey(id);
		
		return dip;
	}
	
	public DataSourceInfoPo queryDataSourceByName(String name) {
		
		DataSourceInfoPo di=dataSourceMapper.selectByName(name);
		
		return di;
	}

	@Override
	public List<DataSourceInfoPo> dataSourceList(Integer pageNum, Integer pageSize) {
		// TODO Auto-generated method stub
		
		PageHelper.startPage(pageNum, pageSize);
		List<DataSourceInfoPo> list=dataSourceMapper.selectAll();
		
		return list;
	}

	@Transactional(rollbackFor = Exception.class)
	public JobGroupPo addJobGroup(JobGroup jg) {
		
		JobGroupPo jgp= jobGroupMapper.selectByName(jg.getName());

		if(Objects.isNull(jgp)) {
		jgp=BeanCopierUtils.copy(jg, new JobGroupPo());
		//jgp.setId(UuidUtils.generateIdString());
		jgp.setCreateTime(new Date());
		jobGroupMapper.insert(jgp);			
		}
		
		if(CollectionUtils.isNotEmpty(jg.getJobList())) {

			List<Long> jobIdlist=jg.getJobList().stream().map(EtlJob::getId).filter(it->Objects.nonNull(it)).collect(Collectors.toList());
			
			if(CollectionUtils.isEmpty(jobIdlist)) {
				
				List<String> jobNamelist=jg.getJobList().stream().map(EtlJob::getJobName).collect(Collectors.toList());
				
				jobIdlist=etlJobMapper.selectNameListForId(jobNamelist);
			}
			
		for (int i = 0; i < jobIdlist.size(); i++) {
		
			JobGroupRelPo jgc=new JobGroupRelPo();
			jgc.setJobId(jobIdlist.get(i));
			jgc.setGorupId(jgp.getId());
			jgc.setSort(i);
			//jgc.setId(UuidUtils.generateIdString());
			jobGroupRelMapper.insert(jgc);
		}
		}
		
		return jgp;
	}
	
	public JobGroup queryJobGroupBy(String name) {
		// TODO Auto-generated method stub
		
		JobGroupPo jgp=jobGroupMapper.selectByName(name);
		
		List<EtlJobPo> jobs=etlJobMapper.selectGroupAvailableJobs(jgp.getId());
		
		List<EtlJob> jobList=jobs.stream().map(jp->queryEtlJob(jp.getJobName())).collect(Collectors.toList());
		
		JobGroup jg=  BeanCopierUtils.copy(jgp, new JobGroup());
		jg.setJobList(jobList);

		return jg;
	}
	
	
	public JobGroup queryJobGroup(String name,String ruleType) {
		// TODO Auto-generated method stub
		
		JobGroupPo jgp=jobGroupMapper.selectByName(name);
		
		List<EtlJobPo> jobs=etlJobMapper.selectGroupAvailableJobs(jgp.getId());
		
		List<EtlJob> jobList=jobs.stream().map(jp->queryEtlJob(jp.getJobName())).collect(Collectors.toList());
		
		JobGroup jg=  BeanCopierUtils.copy(jgp, new JobGroup());
		jg.setJobList(jobList);

		return jg;
	}
	

	
	public List<JobGroupPo> jobGroupList(Integer pageNum, Integer pageSize) {
		// TODO Auto-generated method stub
		PageHelper.startPage(pageNum, pageSize);
		List<JobGroupPo> list=jobGroupMapper.selectAll();

		return list;
	}
	
	
	
	public int addDataMappingColumn(ColumnMappingForm form) {
		// TODO Auto-generated method stub
		
		if(Objects.isNull(form.getDataMappingId())) {
		String jobName= form.getJobName();
		
		Long  jobId=etlJobMapper.selectByName(jobName).getId();
		form.setDataMappingId(dataMappingMapper.selectByJobid(jobId).get(0).getId());
		}

		Integer max=	mappingColumnMapper.selectMaxSortBydmId(form.getDataMappingId());
		
		if(max==null)max=0;
	
		List<ColumnInfo>  sList=form.getSourceColumns();
		List<ColumnInfo>  tList=form.getTargetColumns();
		
		Date cd=new Date();
		
		int up=0;
		
		for (int i = 0; i < sList.size(); i++) {
			
			ColumnInfo t=tList.get(i);
			ColumnInfo s=sList.get(i);

			MappingColumnPo smc=new MappingColumnPo();
			
			smc.setColName(s.getName());
			smc.setColType(s.getType());
			smc.setLength(s.getLength());
			smc.setJdbcType(s.getJdbcType()!=null?s.getJdbcType().toString():null );
			smc.setDecimals(s.getDecimals()!=null?s.getJdbcType().toString():null );
			
			smc.setBelong(SOUR);
			smc.setSort(++max);
			//smc.setId(UuidUtils.generateIdString());
			smc.setCreateTime(cd);
			smc.setDatamappingId(form.getDataMappingId());
			
			MappingColumnPo tmc=new MappingColumnPo();
			
			tmc.setColName(t.getName());
			tmc.setColType(t.getType());
			tmc.setLength(t.getLength());
			tmc.setJdbcType(t.getJdbcType()!=null?t.getJdbcType().toString():null );
			tmc.setDecimals(t.getDecimals()!=null?t.getDecimals().toString():null );
			
			tmc.setSort(max);
			//tmc.setId(UuidUtils.generateIdString());
			tmc.setCreateTime(cd);
			tmc.setBelong(TARG);
			tmc.setDatamappingId(form.getDataMappingId());

			int  it=mappingColumnMapper.insert(smc);
			up=up+it;
			it=mappingColumnMapper.insert(tmc);
			up=up+it;
			
		}

		return up;
	}

	@Override
	public int clearColumnMappingByDmId(Long dataMappingId) {
		// TODO Auto-generated method stub
		
		int i=mappingColumnMapper.deleteByDmId(dataMappingId);
		
		return i;
	}
	
	@Override
	public int clearColumnMappingByJobName(String jobName) {
		// TODO Auto-generated method stub
		
		Long jobId=etlJobMapper.selectByName(jobName).getId();
		
		Long dmId=dataMappingMapper.selectByJobid(jobId).get(0).getId();

		int i=mappingColumnMapper.deleteByDmId(dmId);
		
		return i;
	}
	
	

	@Override
	@Transactional(rollbackFor = Exception.class)
	public JobGroupPo batchAddJobForGroup(BatchJobGroupForm form) {

		 List<BatchDataMappings> batchDataMappings=form.getBatchDataMappings();
		 List<EtlJob> jobList=new ArrayList<>();
		 form.setJobList(jobList);
		 
		 for (BatchDataMappings bdm : batchDataMappings) {
			
			 for (DataMapping dm : bdm.getDataMappings()) {
				 EtlJob job=new EtlJob();
				 job.setJobName(form.getName()+"_JOB_"+dm.getSourceTable().getTable());
				 job.setSource( DataSourceInfo.builder().keyName(form.getSourceKeyName()).build());
				 job.setTarget( DataSourceInfo.builder().keyName(form.getTargetKeyName()).build());
				 
				 List<Pair<ColumnInfo, ColumnInfo>> columnMappingPairs=new ArrayList<>(); 
				
				 TableInfo st= dm.getSourceTable();
				 st.setSchema(bdm.getSourceSchema());
				 TableInfo tt=  dm.getTargetTable();
				 tt.setSchema(bdm.getTargetSchema());

				 if("oracle".equals(dm.getWapperPaged())) {
					 dm.setSourceSql( SqlBuilder.oracleSubSeclt (dm.getSourceSql() ));
				 } 
				 
				 if(ArrayUtils.isNotEmpty(st.getColumns())) {
				 for (int i = 0; i < st.getColumns().length; i++) {
					 
					 ColumnInfo l= st.getColumns()[i];
					 ColumnInfo r=tt.getColumns()[i];
					columnMappingPairs.add(Pair.of(l,r));					
				 	}
				 }
				 dm.setColumnMappingPairs(columnMappingPairs);
				
				 job.setDataMappings(Lists.newArrayList(dm));
				 saveEtlJob(job);				 
				 jobList.add(job);
				 				 
					
					  try {
					  analyseService.analyseJob(JobAnalyseForm.builder().jobName(job.getJobName()).
					  merge(false).missMatchColumn("ignore").build()); } catch (Exception e) { 
						  
					  }
						  // TODO Auto-generated catch block  e.printStackTrace(); }
					 
			 }
			 
		}
		 
		 JobGroupPo jgp= addJobGroup(form);
		
		
		// TODO Auto-generated method stub
		return jgp;
	}


	
	
	
}
